package com.erikpasternak.vader;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.text.TextUtils;
import android.util.Log;

public class DrinkDbHelper extends SQLiteOpenHelper {
    private static final String TAG = "DrinkDb";

    public static final String[] DRINK_PROJECTION = new String[] {
            "_id",
            "name",
            "i1",
            "i2",
            "i3",
            "i4",
            "i5",
            "i6",
            "i7",
            "i8",
            "q1",
            "q2",
            "q3",
            "q4",
            "q5",
            "q6",
            "q7",
            "q8",
            "comment"
    };

    public static final int INDEX_ID = 0;
    public static final int INDEX_NAME = 1;
    public static final int INDEX_I1 = 2;
    public static final int INDEX_I2 = 3;
    public static final int INDEX_I3 = 4;
    public static final int INDEX_I4 = 5;
    public static final int INDEX_I5 = 6;
    public static final int INDEX_I6 = 7;
    public static final int INDEX_I7 = 8;
    public static final int INDEX_I8 = 9;
    public static final int INDEX_Q1 = 10;
    public static final int INDEX_Q2 = 11;
    public static final int INDEX_Q3 = 12;
    public static final int INDEX_Q4 = 13;
    public static final int INDEX_Q5 = 14;
    public static final int INDEX_Q6 = 15;
    public static final int INDEX_Q7 = 16;
    public static final int INDEX_Q8 = 17;
    public static final int INDEX_COMMENT = 18;

    private final Context mContext;
    private static final String DATABASE_CREATE = "create table drinks("
            + "_id integer primary key autoincrement, "
            + "name text, comment text,"
            + "i1 text, i2 text, i3 text, i4 text, i5 text, i6 text, i7 text, i8 text,"
            + "q1 integer, q2 integer, q3 integer, q4 integer, q5 integer, q6 integer, q7 integer, q8 integer"
            + ");";

    public DrinkDbHelper(Context context) {
        super(context, "DrinkList", null, 1);
        mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE);
        InputStream is;
        try {
            is = new FileInputStream(new File(Environment.getExternalStorageDirectory(),
                    "drinks.txt"));
        } catch (FileNotFoundException e) {
            Log.d(TAG, "Horrible news! No drinks file!");
            return;
        }
        populateDb(db, is);
    }

    public static void populateDb(SQLiteDatabase db, InputStream is) {
        CSVParser parser = new CSVParser();
        BufferedReader br = new BufferedReader(new InputStreamReader(is));
        String line = null;
        db.beginTransaction();
        db.delete("drinks", null, null);
        String[] ingredients;
        try {
            if ((line = br.readLine()) != null) {
                ingredients = parser.parseLine(line.toLowerCase());
                if (ingredients.length <= 2) {
                    Log.e(TAG, "No ingredients listed!");
                    return;
                }
            } else {
                return;
            }
            while ((line = br.readLine()) != null) {
                String[] pieces = parser.parseLine(line);
                if (pieces == null || pieces.length < 3) {
                    continue;
                }
                ContentValues vals = new ContentValues();
                vals.put("name", pieces[0].trim());
                vals.put("comment", pieces[1].trim());
                int id = 1;
                for (int i = 2; i < pieces.length
                        && id <= SharedPrefsHelper.MAX_INGREDIENTS; i++) {
                    if (TextUtils.isEmpty(pieces[i].trim())) {
                        continue;
                    }
                    vals.put("i" + id, ingredients[i].trim());
                    vals.put("q" + id, Integer.valueOf(pieces[i].trim()));
                    id++;
                }
                db.insert("drinks", null, vals);
            }
            db.setTransactionSuccessful();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            db.endTransaction();
            if (br != null) {
                try {
                    br.close();
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }

    public Cursor queryDrinks() {
        return getReadableDatabase().query("drinks", DRINK_PROJECTION, null, null, null, null,
                "name");
    }

    public Cursor queryDrinks(CharSequence name) {
        if (TextUtils.isEmpty(name)) {
            return queryDrinks();
        }
        String command = "select * from drinks where name like '%" + name + "%';";
        return getReadableDatabase().rawQuery(command, null);
    }
}
